package com.xcfw.dorm.seviceimpl.dormcenter;

import com.xcfw.dorm.config.MyProps;
import com.xcfw.dorm.entity.Student;
import com.xcfw.dorm.mapper.StudentMapper;
import com.xcfw.dorm.util.DormUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

/**
 * @program: Dorm
 * @description:
 * @author: Bruse Queen
 * @create: 2018-05-05 13:48
 **/
@Service("HandleExcelServiceImpl")
public class HandleExcelServiceImpl {

    @Autowired
    private MyProps myProps;
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private StudentSeriviceImpl studentSerivice;

    private String rootPath;
    private String staticPath;
    private String excelTempPath;
    private String modelPath;


    public HandleExcelServiceImpl() throws IOException {
        rootPath = ResourceUtils.getURL("classpath:").getPath();
        DormUtil.mkFile(rootPath);
        // static路径
        staticPath = rootPath + "static/";
        DormUtil.mkFile(staticPath);
        // 表格临时文件夹
        excelTempPath = rootPath + "static/excel-temp/";
        DormUtil.mkFile(excelTempPath);
        // 文件模版
        modelPath = rootPath + "excel-templates";
        DormUtil.mkFile(modelPath);
    }


    public String outExcel() {
        try {
            List<Student> listStudent = studentMapper.selectActionSql(null, null, null, "博宁8", null, null, null, null);

            List<Map<String, String>> list = new ArrayList<>(6);

            if (listStudent.size() > 0) {
                for (int i = 0; i < 6; i++) {
                    int j = 1;
                    Map<String, String> map = new LinkedHashMap<>();
                    Map<String, String> mapNumber = new HashMap<>();
                    Student studentTemp = new Student();
                    for (Student s : listStudent) {

                        if (s.getDormitory() < (i + 2) * 100 && s.getDormitory() > (i + 1) * 100) {
                            if (!s.getDormitory().equals(studentTemp.getDormitory())) {
                                j = 1;
                            }
                            String name = "n" + String.valueOf(s.getDormitory()).substring(1, 3) + j;
                            String className = "c" + String.valueOf(s.getDormitory()).substring(1, 3) + j;

                            map.put(name, s.getName());
                            map.put(className, s.getStudentClass());
                            j++;
                        }
                        studentTemp = s;
                    }
                    int floor = i + 1;
                    int fullNumbere = map.size() / 2;
                    mapNumber.put(String.valueOf(floor), String.valueOf(fullNumbere));
                    int empty = 156 - fullNumbere;
                    map.put("title", "博宁八入住平面图(" + floor + "楼)");
                    map.put("total", floor + "楼共：156床位 空：" + empty + "个 实住：" + fullNumbere + "人 毕业人数：未统计人");
                    if (i == 5) {
                        int totleBed = 156 * 6;
                        int total = 0;
                        Set<String> strings = mapNumber.keySet();
                        Iterator<String> iterator = strings.iterator();
                        while (iterator.hasNext()) {
                            String s = iterator.next();
                            total += Integer.parseInt(mapNumber.get(s));
                        }
                        int emptyBed = 156 * 6 - total;

                        map.put("building", "博宁8共：" + totleBed + "床位 空：" + total + "个 实住：" + emptyBed + "人 毕业人数：未统计人");
                    }
                    list.add(map);
                }
            }
            String uuid = DormUtil.getUUID();
            String outPath = excelTempPath + uuid + ".xlsx";
            DormUtil.excelReplace(list, modelPath + "/model.xlsx", outPath);

            return myProps.getClientIp() + "excel-temp/" + uuid + ".xlsx";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "成功";
    }


    public List<Student> readExcel(MultipartFile file, String suffix) {
        List<Student> list = new ArrayList<>();
        try {
            String sourceFilePath = getFile(file, suffix);
            // 实例化表格对象
            Workbook wb;
            if (sourceFilePath.matches("^.+\\.(?i)(xlsx)$")) {
                wb = new XSSFWorkbook(new FileInputStream(sourceFilePath));
            } else {
                wb = new HSSFWorkbook(new FileInputStream(sourceFilePath));
            }

            // 遍历取出表格所有的sheet
            List<Sheet> sheet = new ArrayList<>();
            for (Sheet sheetTemp : wb) {
                sheet.add(sheetTemp);
            }
            for (Sheet sheetTemp : sheet) {
                // 遍历sheet
                Iterator rows = sheetTemp.rowIterator();
                // 遍历每行
                outer:
                while (rows.hasNext()) {
                    Row row = (Row) rows.next();
                    // 如果此行不为空
                    if (row != null) {

                        Student student = new Student();
                        int num = row.getLastCellNum();
                        //  去除每个单元格
                        for (int i = 0; i < num; i++) {
                            Cell cell = row.getCell(i);

                            // 不为空设置数据类型
                            if (cell != null) {
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                if ("学号".equals(cell.getStringCellValue())) {
                                    continue outer;
                                }
                            }
                            // 单元格为空进行下次循环
                            if (cell == null || cell.getStringCellValue() == null) {
                                if (i == 0) {
                                    break outer;
                                }
                            } else {
                                // 取出单元格中的值
                                String value = cell.getStringCellValue();
                                switch (i) {
                                    case 0:
                                        student.setSchoolId(Integer.parseInt(value));
                                        break;
                                    case 1:
                                        student.setName(value);
                                        break;
                                    case 2:
                                        if ("男".equals(value)) {
                                            student.setSex(1);
                                        } else {
                                            student.setSex(0);
                                        }
                                        break;
                                    case 3:
                                        student.setGrade(Short.parseShort(value));
                                        break;
                                    case 4:
                                        student.setSeries(value);
                                        break;
                                    case 5:
                                        student.setProfession(value);
                                        break;
                                    case 6:
                                        student.setStudentClass(value);
                                        break;
                                    case 7:
                                        student.setBuilding(value);
                                        break;
                                    case 8:
                                        student.setDormitory(Short.parseShort(value));
                                        break;
                                    case 9:
                                        Map<String, String> map = myProps.getStudentIdentity();
                                        if (!map.isEmpty()) {
                                            for (String key : map.keySet()) {
                                                if (key.equals(value)) {
                                                    student.setIdentity(Integer.parseInt(map.get(key)));
                                                }
                                            }
                                        }
                                        break;
                                    case 10:
                                        student.setIcon(value);
                                        break;
                                    case 11:
                                        student.setPhone(Long.parseLong(value));
                                        break;
                                    case 12:
                                        student.setSchoolPosition(value);
                                        break;
                                    case 13:
                                        student.setAssistant(value);
                                        break;
                                    case 14:
                                        student.setAssistantPhone(Long.parseLong(value));
                                        break;
                                    case 15:
                                        student.setParent(value);
                                        break;
                                    case 16:
                                        student.setParentPhone(Long.parseLong(value));
                                        break;
                                }

                            }
                        }
                        list.add(student);
                    }
                }
                break;
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        list.forEach(l -> {
            studentSerivice.add(l);
        });
        return list;
    }

    public String getFile(MultipartFile file, String suffix) throws IOException {
        // 重新命名表格
        String fileName = DormUtil.getUUID() + suffix;
        File newFile = new File(excelTempPath + fileName);
        file.transferTo(newFile);
        return excelTempPath + fileName;
    }


}
